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 in Database by Edureka
• 9,320 points
12 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 in Database by Edureka
• 8,820 points
51 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 in Database by Edureka
• 8,820 points
24 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 in Database by Edureka
• 9,320 points
13 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,360 points
11,237 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
15,486 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 in Database by Edureka
• 8,820 points
37 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 in Database by Edureka
• 8,820 points
51 views
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15 in Database by Edureka
• 8,820 points
34 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP