in MS excel find first non n a value in row

0 votes

I have dates (Jan-2014 in cell A1, Feb-2015 in cell B1 onwards) in Columns in MS Excel. I am using Hlookup to populate revenue data for those dates in row 2. When using Hlookup, I am getting some #n/a due to missing values for some dates. I need an excel formula so that for those #N/As, it picks up the first available value i.e. if there is #N/A in Cell A2 and B2 and 15 in C2 then I need an excel formula to so that 15 appears in A2 and B2 as well

A1 B1  C1 D1
N/A N/A 15 16

I tried using

=IFERROR(HLOOKUP(A1,Revenue!$C$4:$JH$306,2,0),INDEX($A2:$DI2,MATCH(1,IF(ISNA($A2:$DI2),0,1),0)))

But this didnt work. Also, this is array I would preferably like without array.

Mar 30, 2022 in Database by Edureka
• 13,640 points
153 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 22,940 points
1,572 views
0 votes
1 answer

Why is there still a row limit in Microsoft Excel?

Because of optimizations, most likely. There are ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 22,940 points
149 views
0 votes
0 answers

Excel - Find how many cells are in a range

For each cell in the column I ...READ MORE

Apr 7, 2022 in Database by Edureka
• 13,640 points
69 views
0 votes
1 answer

Find values in a comma separated string in a MySQL query

You can add commas to the left and ...READ MORE

answered Sep 10, 2018 in Database by Sahiti
• 6,380 points
12,040 views
0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
19,836 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 22,940 points
189 views
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 22,940 points
682 views
0 votes
1 answer

Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 22,940 points
117 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP