ISNA VLookup

Microsoft Excel 2003 (full product)
May 10, 2010 at 10:04:31
Specs: Windows XP
=IF(ISNA(VLOOKUP(D2,data!A$3:G$31,2,FALSE)),"",(VLOOKUP(D2,data!A$3:G$31,2,FALSE)))

Data sheet values: A3:G31

Plan SQ Ft HERS Ton Indoor Outdoor
Billy 1676 77 2 I024 x024
Bob 2091 77 2.5 I030 x030
etc

This is the formula I am using, it's looking at a range of HVAC sizes, tonnages and unit model numbers etc.

I'm trying to get it to look at the home "plan" and then pull the corresponding information for that plan which is across the columns in a single row.

What is happening is: it looks at the home plan info in the column, and then it pulls some of the info correctly, but when it comes to the duplicate "Hers Rating" it stops looking across the columns for the rest of that specific plans info and just pulls the info from the 1st "77" it sees. BUT as you can see, there is a difference in the tonnage which makes it necessary to follow the correct ROW and pull THAT info.

How can I make it look beyond the 1st "77" it sees and not stop there?! Any advice would be appreciated!


See More: ISNA VLookup

Report •


#1
May 10, 2010 at 10:13:56
I'm confused by your question.

Your formula tells VLOOKUP to lookup the value found in D2 in the table from data!A3:G31 and return whatever it finds in Column 2 of that table.

In your example it will return 1676 or 2091 dependent on having Billy or Bob in D2.

I don't see how 77 (which is in Column 3) is even involved in the formula.


Report •

#2
May 10, 2010 at 10:21:56
A B C D E F G
Plan SQ Ft HERS Ton Indoor Outdoor Seer
1 Billy 1676 77 2 I024 x024 15
2 Bob 2091 77 2.5 I030 x030 15

In each cell across the columns in the row, I've copied the same vlookup formula =IF(ISNA(VLOOKUP(D2,data!A$3:G$31,2,FALSE)),"",(VLOOKUP(D2,data!A$3:G$31,2,FALSE)))

It find the correct data till it hits the 77 in "billy" then looks no further. Hope that helps explain it a bit better.


Report •

#3
May 10, 2010 at 11:29:20
First: A posting tip:

If you use the pre tags at the top of the comments box, you can line up you data to make it easier to read, as shown here:



    A       B       C    D     E        F       G
   Plan   SQ Ft   HERS  Ton  Indoor  Outdoor  Seer
1 Billy   1676     77    2    I024     x024     15
2 Bob     2091     77    2.5  I030     x030     15

Click the pre button and then enter your data between the tags.

Second: Your formula refers to data!A$3:G$31, yet your example starts in A1. I'll assume that's a typo.

Third: You said:

In each cell across the columns in the row, I've copied the same vlookup formula

If you copy the formula, it is not going to update the col_index_num number, which in your formula is "2". However, it is going to update the reference to the lookup_array, which I don't think is what you want.

If you want to return each piece of data, column by column, associated with the Plan name in Column A, you need to lock the reference to the lookup_array($A$3:$G$31) so that it always looks at the first column (A). Then you can increment the col_index_num so that it return values from the different columns.

For SqFt:

=IF(ISNA(VLOOKUP(D2,data!$A$3:G$31,2,FALSE)),"",(VLOOKUP(D2,data!$A$3:G$31,2,FALSE)))

For HERS:

=IF(ISNA(VLOOKUP(D2,data!$A$3:G$31,3,FALSE)),"",(VLOOKUP(D2,data!$A$3:G$31,3,FALSE)))

For Ton:

=IF(ISNA(VLOOKUP(D2,data!$A$3:G$31,4,FALSE)),"",(VLOOKUP(D2,data!$A$3:G$31,4,FALSE)))

etc.


Report •

Related Solutions

#4
May 10, 2010 at 12:31:45
You're A GENIUS! Thank you SO much for your help and the pointers!

Report •


Ask Question