• 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.

vlookup value across two columns - return multiple values

C

CR1989

New Member
i have a value that appears multiple times in the first two columns of my data set. i want to be able to return its corresponding value in the third column i f the value appears in the first column, and want to be able to return the value in the fourth column if it appears in the second, all in one single column with no blanks. is there any way of doing this? i tried a nested if statement but it just returned blanks for the rows where the lookup value does not appear, meaning there are large gaps between the returned values. i also tried a more complicated one using index small and row but althpough has no blanks only returns the values across two columns, ie all the corresponding values from the third and fourth columns side by side. it seems just to be one or the other and i cant simply just get all the values appearing consecutively with no spaces under a single column.

Basically i have the follwoing:

1 2 3 4 (col numbers)
A X 0 1
X X 3 4
X X 2 2
X A 2 1
X X 3 2
A X 2 1

and i want to return the follwoing:

For "A":
1 (col number)
0
1
2

with no gaps in between. any ideas?
 
Hi

I didnt do anything for these numbers, they wer just part of my data set. its just that when A is in the first column, the figures in the 3rd column relate to it, when it appears in the 2nd column, the figures in the fourth column relate to it. bear in mind this is a highly simplified version of my excel sheet, i just wanted to demonstrate that, for a given value, to return a number in a different column depending on which column the value appears in the first place.
 
Assumption:
original data is in A1:D6
Value to be found is in F1

Confirm this formula as an array using Ctrl+Shift+Enter, not just Enter, and copy down as far as needed.
=IF(ROWS(A$1:A1)>COUNTIF($A$1:$B$6,$F$1),"",IF(ROWS(A$1:A1)<=COUNTIF($A$1:$A$6,$F$1),INDEX(C:C,SMALL(IF($A$1:$A$6=$F$1,ROW($A$1:$A$6)),ROW(A1))),INDEX(D:D,SMALL(IF($B$1:$B$6=$F$1,ROW($B$1:$B$6)),ROW(A1)+1-ROWS(A$1:A1)))))

Only adjust the ranges in bold. The other ranges are used as counters.
 
i see what is going wrong. the above formula returns ALL of the values in the third column where A appears in the first column, then it goes and returns all of the values form the fourth column where it appears in the second column.

the above returns:
0
2
1

where as i want to return
0
1
2

in that order. sorry I should have specified that i want to return the values in order as they appear, rather than the way the above formula returns it. is there any way to do this?
 
Can we create some helper columns? It would be a lot easier if we could have a couple columns like:
=IF(A2=MyValue,A2,B2)
and
=IF(A2=MyValue,C2,D2)

That would get all the information into a single column, and we could then use our INDEX array formula to extract the desired values.
 
i was told to use this before. the thing is i have loads of other values to do this besides "A" meaning my spread sheet would get very messy otherwise so i am trying to avoid having to do it this way! unfortunately this seems to be the only way of going about it. thanks for the help anyway
 
You must log in or register to reply here.
Back
Top