vlookup does not solve the purpose

[gsvirdi]

Hello,


I am having a wonderfully huge list of values having lot of data like:

[pre]

head1  head2 ..... headX
value1 value2..... valueX
value1 value2..... valueX
value3 value4..... valueX
value1 value2..... valueX
value3 value4..... valueX
value3 value4..... valueX
value1 value2..... valueX[/pre]
and the list goes down for like another 11000 rows.


I was trying to fetch all "value1" using VLOOKUP, and I used =VLOOKUP($IV$1,'LIST'!B3:AB3,3,FALSE)

but I am geting #N/A values where the list data do not match with the required value ($IV$1). So the row containing "Value3" shows #N/A in all cells.

I can obviously use Filter afterwards, but that will create problem with serial number (11000 rows) bcoz we can not drag and fill values when Filter is applied (On).

What/How should I do in this case?

 

[mahaveer]

yes you can drag dear but first follow the step before drag


step1. make filter and select one item from filter

step2. press Ctrl + A i.e. select all the cells

step3. then press Alt+H+AL (only in excel 2007 i.e. "Left Allignment")

step4. now you can drag...

step5. now remove the filter and check....

its so easy

 

[jason]

What is in the 3rd column of your 'TableArray' (B3:AB3)


if i am following your formula correctly, it will start in B3 and try to find the value in IV1. because you are spcifinig False (exact match) if b3 does not contain the same value that you are looking for, the function will not be able to return anything (cuz it wont find it).


what you may want to do is use the IF function along with the vlookup

maybe something as simple as adding =IF(b3=IV1, Vlookup(IV1,'LIST'!(B3:AB3),3,False), _______) is this last part, you can specifiy the fucntion to do something else.... like return the text "not found"...or another function/calculation.


in the end, this will be able to pull the value in the 3rd col when b3 equals IV1


does that sound like it may be what you're after?