• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vertical Lookups across Multiple Columns

A

Andrewma

New Member
I am trying to find an elegant way to do the following:

My data currently looks like the following.


---------Column A----Column B----Column C----Column D

Row1-----Apple---------7----------Peach---------5

Row2-----Peach---------10---------Apple---------6

Row3-----Apple---------3----------Peach---------1


Column B shows the quantity of Apples or Peaches of the column preceding it.

For example: If I looked at Row 1 only, we have 7 apples and 5 peaches.


I need to format the data to so If I wanted to Show ONLY the number of apples as such:


--------Column A------Column B

Row1------Apple---------7

Row2------Apple---------3

Row3------Apple---------6


The standard VLOOKUP does not work here as it will find only the first instance of Apple in any column.

I am currently using a Function called VLOOKUPall (found here: http://www.sulprobil.com/html/lookup-variants.html#Vlookupall)


Although this does work, it does not allow any type of search across multiple columns.


There must be a better solution???
 
Question: What does Column A and C represent? ...wonering why you could have the fruits items listed in either column for the same row...


EDIT:

This will depend solely on your layout and not see such, this may not work.


Assuming your previous setup/layout of data starts in A1:Dwhatever: i have the following starting in A10.

Apple | =IF(A1="Apple",VLOOKUP(A10,A1:B1,2,FALSE),VLOOKUP(A10,C1:D1,2,FALSE))

Apple | =IF(A2="Apple",VLOOKUP(A11,A2:B2,2,FALSE),VLOOKUP(A11,C2:D2,2,FALSE))

Apple | ...and so on... didnt write each formula separately, just used the fill handle


If a row doesnt have apple in either column, the "#NA" error is displayed. this could easily be fixed with another If statement.

but for my quick sample it seems to be doing what you're describing......
 
This is just the way I have to deal with it. Column A & C will never have the same info on the same row of course.


The ugly workaround I Have been using is to make another extremely long column which basically stacks the Column A & C on top of each other, but that takes thousands of rows to do it, and its not pretty.
 
im sure someone will come along and clean up or give a better option (luke?!? lol)


my change to the formula in the event neither column contains 'apples':

=IF(A1=A10,VLOOKUP(A10,A1:B1,2,FALSE),IF(C1=A10,VLOOKUP(A10,C1:D1,2,FALSE),"NO APPLES"))
 
You must log in or register to reply here.
Back
Top